#!usr/bin/env python
# -*- coding:utf-8 -*-
"""
    Utility functions for copying and archiving files and directory trees.
    
    XXX The functions here don't copy the resource fork or other metadata on Mac.

"""

import os
import sys
import shutil
import logging
from xlutils.copy import copy
from commFunc import getfilelist, nmb_of
from xlrd import open_workbook, Book, biffh
from public_parameters import WRITE_VALID_HEAD_COLUMN_NAME, \
                              TSO_FILE_PATH, \
                              TEMPLATE_FILE_PATH


def wt_case_xls_file(capfileName):
    line_num = 20
    rownum = 1
    test_file =checkXLS(TEMPLATE_FILE_PATH, capfileName)
    obj = WTXLS(TSO_FILE_PATH)
    cap_dyn_tr = obj.parser_the_sheet("cap_dyn_tree")
    for k,v in cap_dyn_tr.iteritems():
         #---------------- 添加“/”（以capfileName.lower()加左侧“/”的形式在config文件夹中的tso文件下查找cap）---------------------（chao yi 2017-4-7）
        if "/"+ capfileName.lower() in k.lower():
         #---------------- 添加“/”（以capfileName.lower()加左侧“/”的形式在config文件夹中的tso文件下查找cap）---------------------（chao yi 2017-4-7）
            init_nm = "/DevMgmt/%s.xml" % capfileName.replace('Cap','Dyn')
            xml_file_name = cap_dyn_tr.get(k, init_nm)
            break
            
    
    # rb = open_workbook(r'E:\test\tries\Farad_LEDM_Test_tmplate.xls', formatting_info=True)
    # rb = open_workbook(templat_file, formatting_info=True)
    # wb = copy(rb)
    # ws = wb.get_sheet(0)
    wtxls = WTXLS(test_file)
    for i, item in enumerate(WRITE_VALID_HEAD_COLUMN_NAME):
        wtxls.wt_xls("Copy", row_x=line_num, col_y=i, value=[item])
        wtxls.wt_xls("Copy", row_x=line_num, col_y=4, value=[xml_file_name])
        # ws.write(line_num, i, item)
    line_num = line_num + 1
    for fl in getfilelist(os.path.realpath(os.path.join('.', 'testMaterial','Tickets',capfileName)), []):
        filename = os.path.basename(fl)
        # is_out_file = lambda x:filename.find("readWrite")
        # do somethin to deal with filename
        elenames = filename.split("_");
        elename = elenames[0] + ":" + elenames[1];

        valid_put_des = "Put element " + elename + " as valid value for the tree " + xml_file_name
        valid_get_des = "Get  the tree " + xml_file_name + " and chenk element " + elename
        valid_get_exp = "The response code of HTTPClient should return 200 OK and the element " + elename + " modify successfully."
        invalid_put_des = "Put element " + elename + " as invalid value for the tree " + xml_file_name
        invalid_get_des = "Get  the tree " + xml_file_name + " and chenk element " + elename + "."
        invalid_get_exp = "The response code of HTTPClient should return 200 OK and the element " + elename + " shouldn't modify successfully."

        if filename.__contains__("readOnly"):
            continue
        else:
            wtxls.wt_xls("Copy", row_x=line_num, col_y=0, value=["Copy"])
            wtxls.wt_xls("Copy", row_x=line_num, col_y=1, value=["Asset"])
            wtxls.wt_xls("Copy", row_x=line_num, col_y=2, value=[xml_file_name.split('.')[0].split('/')[-1]])
            wtxls.wt_xls("Copy", row_x=line_num, col_y=3, value=[str(rownum)])
            
            #copy001
            wtxls.wt_xls("Copy", row_x=line_num, col_y=9, value=[nmb_of(str(rownum))])
            
            wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=0, value=["Copy"])
            wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=1, value=["Asset"])
            wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=2, value=[xml_file_name.split('.')[0].split('/')[-1]])
            wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=3, value=[str(rownum+1)])
            
            #copy001
            wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=9, value=[nmb_of(str(rownum+1))])
            
            wtxls.wt_xls("Copy", row_x=line_num+ 1, col_y=4, value=["GET"])
            wtxls.wt_xls("Copy", row_x=line_num+ 1, col_y=7, value=[capfileName+os.sep+filename])
            wtxls.wt_xls("Copy", row_x=line_num, col_y=4, value=["PUT"])
            wtxls.wt_xls("Copy", row_x=line_num, col_y=8, value=[capfileName+os.sep+filename])
            if filename.__contains__("NOK"):
                wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=5, value=[invalid_get_des])
                wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=6, value=[invalid_get_exp])
                wtxls.wt_xls("Copy", row_x=line_num, col_y=5, value=[invalid_put_des])
                wtxls.wt_xls("Copy", row_x=line_num, col_y=6,
                             value=["The response code of HTTPClient should return 400 bad Request."])
            elif filename.__contains__("_OK_"):
                wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=5, value=[valid_get_des])
                wtxls.wt_xls("Copy", row_x=line_num + 1, col_y=6, value=[valid_get_exp])
                wtxls.wt_xls("Copy", row_x=line_num, col_y=5, value=[valid_put_des])
                wtxls.wt_xls("Copy", row_x=line_num, col_y=6,
                             value=["The response code of HTTPClient should return 200 OK."])
        rownum += 2
        line_num += 2
        # wtxls.save(test_file)

def checkXLS(templat_file,capfileName,willmake=True):
    cap_xls_Name = os.path.basename(templat_file).replace("Farad_LEDM_Test_tmplate","LEDM_"+capfileName)
    cap_xls_Path = os.path.realpath(os.path.join('.', 'testMaterial',cap_xls_Name))
    if os.path.exists(cap_xls_Path):
        try:
            os.remove(cap_xls_Path)
        except WindowsError as e:
            # use unlocker to kill process
            pass
    if willmake:
        if os.path.isfile(templat_file):
            #os.renames(templat_file,cap_xls_Path)
            try:
                shutil.copy(templat_file, cap_xls_Path)
            except WindowsError as e:
                pass
    return cap_xls_Path

class WTXLS(Book):
    def __init__(self, srcxmlfile, cell_overwrite_ok=True, formatting_info=True):
        
        self.sheetObj = None
        self.srcxmlfile = srcxmlfile
#        self.destxmlfile = destxmlfile
#         self.sheetname = sheetname
#         self.value_row = value_row
#         self.value_col = value_col
        self.will_save = True
        self.cell_overwrite_ok = True
        self.formatting_info = True
        self.op_book = open_workbook(self.srcxmlfile, formatting_info=self.formatting_info)
        self.wb = copy(self.op_book)
    
    def __del__(self):
        try:
            if self.will_save:
                bsnm = os.path.basename(self.srcxmlfile)
                logging.info("Saving my edition of the file [%s]. " % bsnm)
                #self.wb.save(r'bbbbbbbbbbbbbbb.xls')
                self.wb.save(self.srcxmlfile)
        except IOError as e:
            logging.error("File [%s] is opened or used by others. close and try again." % bsnm)
            
    def is_valid_xls_file(self):
        if os.path.isfile(self.srcxmlfile):
            if self.srcxmlfile.endswith("xls"):
                try:
                    Book.encoding = "utf8" #if sheet name contains Chinese chracters, will be occure exception.
                    #self.op_book = open_workbook(self.srcxmlfile, formatting_info=self.formatting_info)
                    if self.op_book.nsheets <=0:
                        return False
                except Exception as e:
                    print "occure exception when checking file validation: ", str(e)
                    return False
                with open(self.srcxmlfile, "rb") as f:
                    f.seek(0, 2) # EOF
                    size = f.tell()
                    f.seek(0, 0) # BOF
                    if size == 0:
                        raise biffh.XLRDError("File size is 0 byte")     
            else:
                print "File not end up with '.xls' perfix."
                return False
        return True
    
    def get_sheet_lst(self):
        #return self.op_book.sheets()
        return self.op_book.sheet_names()
    
    def get_sht_obj(self, shtnm):
        if not self.op_book:
            self.op_book = open_workbook(self.srcxmlfile, formatting_info=self.formatting_info)
        if not shtnm in self.get_sheet_lst():
            raise IOError("The input sheet name is not in current sheet list.")
        self.sheetObj = self.op_book.sheet_by_name(shtnm)
        return self.sheetObj
    
    def get_position_value(self, shtnm, row_pos, col_pos):
        vs = sys.version.split()[0]
        if not int(vs.split('.')[0]) == 2 or int(vs.split('.')[1]) < 3:
            print "basestring type starts from version2.3 but removed from version3.0"
            return None
        
        if not isinstance(shtnm, basestring) or not isinstance(row_pos, int) or not isinstance(col_pos, int) or \
           not shtnm in self.get_sheet_lst():
            print "The input param(s) is/are not valid."
            return None
        
        if row_pos > self.get_row_len(shtnm) or col_pos > self.get_col_len(shtnm):
            print "Your request position (%s, %s) is out of line." % (row_pos, col_pos)
            return None
        
        return self.get_sht_obj(shtnm).cell(row_pos, col_pos).value
    
    def parser_the_sheet(self, shtnm):
        '''
            shtnm: name of the sheet page to be parsered.
            note: currently assitance for whichever page having no merged cells. 
            return: {}
        '''
        self.will_save = False
        cap_dyn_map = {}
        for row in xrange(self.get_row_len(shtnm)):
            if not cap_dyn_map.has_key(self.get_position_value(shtnm, row, 0)):
                cap_dyn_map.update({self.get_position_value(shtnm, row, 0):self.get_position_value(shtnm, row, 1)})
            #cap_dyn_map[self.get_position_value(shtnm, row, 0)] = self.get_position_value(shtnm, row, 1)
        return cap_dyn_map

    def wt_xls(self, sheetname=None, **kwargs):
        '''row_x = x, col_y = y, value = [["a","b",]]'''
        
        
        if sheetname == None:
            sheetname = "Copy"
            print "Found no valid sheet names, use %s instead." % sheetname
        if sheetname not in self.get_sheet_lst():
            self.wb.add_sheet(sheetname)
            self.get_sheet_lst().append(sheetname)
        sheet_index = self.get_sheet_lst().index(sheetname)
        
        ws = self.wb.get_sheet(sheet_index)
        value = kwargs.get("value",[[]])
        rowx = kwargs.get("row_x", "NULL")
        coly = kwargs.get("col_y", "NULL")
        
        if rowx == "NULL" and coly == "NULL":
            '''write sheet from (0, 0) to (rowx, coly)'''
            for i in xrange(len(value)):
                for j in xrange(len(i)):
                    ws.write(i, j, value[i][j])
        else:
            if rowx != "NULL" and coly == "NULL":
                '''write sheet from (rowx, 0) to (rowx, n)'''
                for i in value:
                    try:
                        coly = 0
                        for j in i:
                            ws.write(rowx, coly, j)
                            coly += 1
                    except Exception as e:
                        print str(e)
                  
            else:      
                if rowx == "NULL" and coly != "NULL":
                    '''write sheet from (0, coly) to (n, coly)'''
                    for i in value:
                        try:
                            row_x = 0
                            for j in i:
                                ws.write(row_x, coly, j)
                                row_x += 1 
                        except Exception as e:
                            print str(e)
                    
                else:       
                    if rowx != "NULL" and coly != "NULL":
                        '''write sheet to (rowx, coly)'''
                        for i in value:
                            if len(i) == 1:
                                for j in i:
                                    ws.write(rowx, coly, j)
                            else:
                                ws.write(rowx, coly, i)

    def get_row_len(self, shtnm):
        '''
        return the total row lenth of a sheet named "sheetname".
        input: the sheet name, type:xxx
        output:the row lenth, type:int
        '''
        return self.get_sht_obj(shtnm).nrows
    
    def get_col_len(self, shtnm):
        '''
        return the total col lenth of a sheet named "sheetname".
        input: the sheet name, type:xxx
        output:the colume lenth, type:int
        '''
        return self.get_sht_obj(shtnm).ncols
    
    def get_row_values(self, shtnm, row_pos):
        return self.get_sht_obj(shtnm).row_values(row_pos)
    
    def get_col_values(self, shtnm, col_pos):
        return self.get_sht_obj(shtnm).row_values(col_pos)
    
def demo_WTXLS():
    VALID_HEAD_COLUMN_NAME = ('Copy2',
                              'Asset2',
                              'Put-Post-Get2',
                              '',
                              '/Copy/CopyConfigCap2.xml',
                              'Description2', 
                              'Expected Behavior2', 
                              'Output File2', 
                              'Input File2')
    line_num = 20
    srcfl = r'E:\test\tries\Farad_LEDM_Test_tmplate.xls'
    wtxml = WTXLS(srcfl)
    
    if wtxml.is_valid_xls_file():
        wtxml.wt_xls(sheetname="Copy", row_x = line_num, value=[list(VALID_HEAD_COLUMN_NAME)])
        
    for fl in getfilelist(r"E:\test\tickets",[]):
        fl = os.path.basename(fl)
        print wtxml.get_position_value("Copy", line_num+1, 4)
        if wtxml.get_position_value("Copy", line_num+1, 4).upper()=="GET":
            wtxml.wt_xls("Copy",row_x=line_num+1, col_y=7,value=[[fl]])
        else:
            wtxml.wt_xls("Copy",row_x=line_num+1, col_y=8,value=[[fl]])
            
        line_num += 1
  
if __name__=='__main__':
    #pass
    #demo_WTXLS()
    capfileName ="ConsumableConfigSecureCap"
    wt_case_xls_file(capfileName)


